package dbrpc

import (
	"bytes"
	"database/sql"
	"dub/define/grpcmodel"
	"dub/utils"
	"fmt"
	"golang.org/x/net/context"
	"time"
)

type DbRpcMem struct {
	log *utils.Logger
}

//修改用户的信息,这些属于用户属性,添加人是不会修改的
func (m *DbRpcMem) Update(ctx context.Context, args *grpcmodel.ModelDbRpcMemReqModel) (reply *grpcmodel.ModelDbRpcMemResUpdate, err error) {
	reply = &grpcmodel.ModelDbRpcMemResUpdate{}
	after_from_buf := bytes.Buffer{}
	params := make([]interface{}, 0)
	after_from_buf.WriteString("update dubmem set memEditTime=now()")

	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemName() != "") {
		after_from_buf.WriteString(",memName=?")
		params = append(params, args.GetModelDbRpcMem().GetMemName())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemPhone() != "") {
		after_from_buf.WriteString(",memPhone=?")
		params = append(params, args.GetModelDbRpcMem().GetMemPhone())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemBirthDate() > 0) {
		after_from_buf.WriteString(",memBirthDate=?")
		birthday := time.Unix(int64(args.GetModelDbRpcMem().GetMemBirthDate()), 0).Format("2006-01-02")
		params = append(params, birthday)
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemWxCode() != "") {
		after_from_buf.WriteString(",memWxCode=?")
		params = append(params, args.GetModelDbRpcMem().GetMemWxCode())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemQqCode() != "") {
		after_from_buf.WriteString(",memQqCode=?")
		params = append(params, args.GetModelDbRpcMem().GetMemQqCode())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemSex() != "") {
		after_from_buf.WriteString(",memSex=?")
		params = append(params, args.GetModelDbRpcMem().GetMemSex())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemHeight() > 0) {
		after_from_buf.WriteString(",memHeight=?")
		params = append(params, args.GetModelDbRpcMem().GetMemHeight())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemEducation() != "") {
		after_from_buf.WriteString(",memEducation=?")
		params = append(params, args.GetModelDbRpcMem().GetMemEducation())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemLiveRegionCode() != "") {
		after_from_buf.WriteString(",memLiveRegionCode=?")
		params = append(params, args.GetModelDbRpcMem().GetMemLiveRegionCode())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemCensusRegionCode() != "") {
		after_from_buf.WriteString(",memCensusRegionCode=?")
		params = append(params, args.GetModelDbRpcMem().GetMemCensusRegionCode())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemIncome() > 0) {
		after_from_buf.WriteString(",memIncome=?")
		params = append(params, args.GetModelDbRpcMem().GetMemIncome())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemJob() != "") {
		after_from_buf.WriteString(",memJob=?")
		params = append(params, args.GetModelDbRpcMem().GetMemJob())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemShowPics() != "") {
		after_from_buf.WriteString(",memShowPics=?")
		params = append(params, args.GetModelDbRpcMem().GetMemShowPics())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemHouse() != "") {
		after_from_buf.WriteString(",memHouse=?")
		params = append(params, args.GetModelDbRpcMem().GetMemHouse())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemCar() != "") {
		after_from_buf.WriteString(",memCar=?")
		params = append(params, args.GetModelDbRpcMem().GetMemCar())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemLabelIds() != "") {
		after_from_buf.WriteString(",memLabelIds=?")
		params = append(params, args.GetModelDbRpcMem().GetMemLabelIds())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemRequirement() != "") {
		after_from_buf.WriteString(",memRequirement=?")
		params = append(params, args.GetModelDbRpcMem().GetMemRequirement())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemEvaluation() != "") {
		after_from_buf.WriteString(",memEvaluation=?")
		params = append(params, args.GetModelDbRpcMem().GetMemEvaluation())
	}

	after_from_buf.WriteString(" where memId=?")
	params = append(params, args.GetModelDbRpcMem().GetMemId())

	dbAccounts := NewDbProxy().Get(DB_Accounts)
	sql_str := after_from_buf.String()
	m.log.Infof("def_func_db_mem.go FindByName method exec %s params is %+v\n", sql_str, params)
	res, err := dbAccounts.Exec(sql_str, params...)
	for {
		if err != nil {
			m.log.Infof("def_func_db_mem.go Update method dbAccounts.Exec err. %v\n", err)
			reply.Err = 2
			break
		}

		up_count, err := res.RowsAffected()
		if err != nil {
			m.log.Infof("def_func_db_mem.go Update method res.RowsAffected err. %v\n", err)
			reply.Err = 2
			break
		}
		if up_count < 1 {
			reply.Err = 1
		}
		break
	}
	return
}

//添加一个会员信息
func (m *DbRpcMem) Add(ctx context.Context, args *grpcmodel.ModelDbRpcMemReqModel) (reply *grpcmodel.ModelDbRpcMemResAdd, err error) {
	reply = &grpcmodel.ModelDbRpcMemResAdd{}
	sql_pre_buf := bytes.Buffer{}
	sql_pre_buf.WriteString("insert into dubmem (memAddTime,memEditTime,memHeight,memIncome,memStatus,memAddType,memAddUseId")
	sql_aft_buf := bytes.Buffer{}
	sql_aft_buf.WriteString(") values(now(),now(),?,?,?,?,?")
	params := make([]interface{}, 0)
	params = append(params, args.GetModelDbRpcMem().GetMemHeight())
	params = append(params, args.GetModelDbRpcMem().GetMemIncome())
	params = append(params, args.GetModelDbRpcMem().GetMemStatus())
	params = append(params, args.GetModelDbRpcMem().GetMemAddType())
	params = append(params, args.GetModelDbRpcMem().GetMemAddUseId())

	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemName() != "") {
		sql_pre_buf.WriteString(",memName")
		sql_aft_buf.WriteString(",?")
		params = append(params, args.GetModelDbRpcMem().GetMemName())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemPhone() != "") {
		sql_pre_buf.WriteString(",memPhone")
		sql_aft_buf.WriteString(",?")
		params = append(params, args.GetModelDbRpcMem().GetMemPhone())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemBirthDate() > 0) {
		sql_pre_buf.WriteString(",memBirthDate")
		sql_aft_buf.WriteString(",?")
		birthday := time.Unix(int64(args.GetModelDbRpcMem().GetMemBirthDate()), 0).Format("2006-01-02")
		params = append(params, birthday)
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemWxCode() != "") {
		sql_pre_buf.WriteString(",memWxCode")
		sql_aft_buf.WriteString(",?")
		params = append(params, args.GetModelDbRpcMem().GetMemWxCode())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemQqCode() != "") {
		sql_pre_buf.WriteString(",memQqCode")
		sql_aft_buf.WriteString(",?")
		params = append(params, args.GetModelDbRpcMem().GetMemQqCode())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemSex() != "") {
		sql_pre_buf.WriteString(",memSex")
		sql_aft_buf.WriteString(",?")
		params = append(params, args.GetModelDbRpcMem().GetMemSex())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemEducation() != "") {
		sql_pre_buf.WriteString(",memEducation")
		sql_aft_buf.WriteString(",?")
		params = append(params, args.GetModelDbRpcMem().GetMemEducation())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemLiveRegionCode() != "") {
		sql_pre_buf.WriteString(",memLiveRegionCode")
		sql_aft_buf.WriteString(",?")
		params = append(params, args.GetModelDbRpcMem().GetMemLiveRegionCode())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemCensusRegionCode() != "") {
		sql_pre_buf.WriteString(",memCensusRegionCode")
		sql_aft_buf.WriteString(",?")
		params = append(params, args.GetModelDbRpcMem().GetMemCensusRegionCode())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemJob() != "") {
		sql_pre_buf.WriteString(",memJob")
		sql_aft_buf.WriteString(",?")
		params = append(params, args.GetModelDbRpcMem().GetMemJob())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemShowPics() != "") {
		sql_pre_buf.WriteString(",memShowPics")
		sql_aft_buf.WriteString(",?")
		params = append(params, args.GetModelDbRpcMem().GetMemShowPics())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemHouse() != "") {
		sql_pre_buf.WriteString(",memHouse")
		sql_aft_buf.WriteString(",?")
		params = append(params, args.GetModelDbRpcMem().GetMemHouse())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemCar() != "") {
		sql_pre_buf.WriteString(",memCar")
		sql_aft_buf.WriteString(",?")
		params = append(params, args.GetModelDbRpcMem().GetMemCar())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemLabelIds() != "") {
		sql_pre_buf.WriteString(",memLabelIds")
		sql_aft_buf.WriteString(",?")
		params = append(params, args.GetModelDbRpcMem().GetMemLabelIds())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemRequirement() != "") {
		sql_pre_buf.WriteString(",memRequirement")
		sql_aft_buf.WriteString(",?")
		params = append(params, args.GetModelDbRpcMem().GetMemRequirement())
	}
	if args.GetIsAllUpdate() || (!args.GetIsAllUpdate() && args.GetModelDbRpcMem().GetMemEvaluation() != "") {
		sql_pre_buf.WriteString(",memEvaluation")
		sql_aft_buf.WriteString(",?")
		params = append(params, args.GetModelDbRpcMem().GetMemEvaluation())
	}
	sql_aft_buf.WriteString(")")
	sql_pre_buf.WriteString(sql_aft_buf.String())

	dbAccounts := NewDbProxy().Get(DB_Accounts)
	sql_str := sql_pre_buf.String()
	m.log.Infof("def_func_db_mem.go Add method exec %s params is %+v\n", sql_str, params)
	res, err := dbAccounts.Exec(sql_str, params...)
	for {
		if err != nil {
			reply.Err = 2
			m.log.Infof("def_func_db_mem.go Add method dbAccounts.Exec err. %v\n", err)
			break
		}

		up_id, err := res.LastInsertId()
		if err != nil {
			reply.Err = 2
			break
		}

		reply.MemId = uint32(up_id)
		reply.Err = 0
		break
	}
	return
}

//根据查询条件查询一个会员信息
func (m *DbRpcMem) FindMem(ctx context.Context, args *grpcmodel.ModelDbRpcMemReqFind) (reply *grpcmodel.ModelDbRpcMemResFindMem, err error) {
	reply = &grpcmodel.ModelDbRpcMemResFindMem{}
	after_from_buf := bytes.Buffer{}
	params := make([]interface{}, 0)
	after_from_buf.WriteString("from dubmem where memStatus>0")
	for {
		if args.GetIsRepeat() {
			//判断是否是重复查询
			after_from_buf.WriteString(" and (m.memPhone=? or m.memWxCode=? or m.memQqCode=?) limit 1")
			params = append(params, args.GetMemPhone(), args.GetMemWxCode(), args.GetMemQqCode())
			sql_str := fmt.Sprintf("select memId %s", after_from_buf.String())
			m.log.Infof("def_func_db_mem.go FindMem method exec %s params is %+v\n", sql_str, params)
			dbAccounts := NewDbProxy().Get(DB_Accounts)
			row := dbAccounts.QueryRow(sql_str, params...)
			var memId sql.NullInt64
			err := row.Scan(&memId)
			if err == sql.ErrNoRows {
				err = nil
				reply.Repeat = false
				break
			}
			if err != nil {
				reply.Err = 2
				m.log.Infof("def_func_db_mem.go FindMem method row.Scan err. %v\n", err)
				break
			}
			reply.Repeat = true
			break
		}

		if args.GetMemId() > 0 {
			after_from_buf.WriteString(" and memId=?")
			params = append(params, args.GetMemId())
		}

		after_from_buf.WriteString(" limit 1")

		dbAccounts := NewDbProxy().Get(DB_Accounts)
		sql_str := fmt.Sprintf("select memId, memName, memPhone, UNIX_TIMESTAMP(memBirthDate), memWxCode, memQqCode, memSex, memHeight, memEducation, memLiveRegionCode, memCensusRegionCode, memIncome, memJob, memShowPics, memHouse, memCar, memLabelIds, memRequirement, memEvaluation, memStatus, memAddType, memAddUseId, UNIX_TIMESTAMP(memAddTime), UNIX_TIMESTAMP(memEditTime) %s", after_from_buf.String())
		m.log.Infof("def_func_db_mem.go FindMem method exec %s params is %+v\n", sql_str, params)
		row := dbAccounts.QueryRow(sql_str, params...)
		var (
			memId, memHeight, memIncome, memStatus, memAddType, memAddUseId, memAddTime, memEditTime, memBirthDate                                                                                   sql.NullInt64
			memName, memPhone, memWxCode, memQqCode, memSex, memEducation, memLiveRegionCode, memCensusRegionCode, memJob, memShowPics, memHouse, memCar, memLabelIds, memRequirement, memEvaluation sql.NullString
		)
		err := row.Scan(&memId, &memName, &memPhone, &memBirthDate, &memWxCode, &memQqCode, &memSex, &memHeight, &memEducation, &memLiveRegionCode, &memCensusRegionCode, &memIncome, &memJob, &memShowPics, &memHouse, &memCar, &memLabelIds, &memRequirement, &memEvaluation, &memStatus, &memAddTime, &memAddUseId, &memAddTime, &memEditTime)
		if err == sql.ErrNoRows {
			err = nil
			reply.Err = 1
			break
		}
		if err != nil {
			reply.Err = 2
			m.log.Errorf("def_func_db_mem.go FindMem method row.Scan err. %v\n", err)
			break
		}

		model := grpcmodel.ModelDbRpcMem{}
		if memId.Valid {
			model.MemId = uint32(memId.Int64)
		}
		if memName.Valid {
			model.MemName = memName.String
		}
		if memPhone.Valid {
			model.MemPhone = memPhone.String
		}
		if memBirthDate.Valid {
			model.MemBirthDate = uint64(memBirthDate.Int64)
		}
		if memWxCode.Valid {
			model.MemWxCode = memWxCode.String
		}
		if memQqCode.Valid {
			model.MemQqCode = memQqCode.String
		}
		if memSex.Valid {
			model.MemSex = memSex.String
		}
		if memHeight.Valid {
			model.MemHeight = uint32(memHeight.Int64)
		}
		if memEducation.Valid {
			model.MemEducation = memEducation.String
		}
		if memLiveRegionCode.Valid {
			model.MemLiveRegionCode = memLiveRegionCode.String
		}
		if memCensusRegionCode.Valid {
			model.MemCensusRegionCode = memCensusRegionCode.String
		}
		if memIncome.Valid {
			model.MemIncome = uint32(memIncome.Int64)
		}
		if memJob.Valid {
			model.MemJob = memJob.String
		}
		if memShowPics.Valid {
			model.MemShowPics = memShowPics.String
		}
		if memHouse.Valid {
			model.MemHouse = memHouse.String
		}
		if memCar.Valid {
			model.MemCar = memCar.String
		}
		if memLabelIds.Valid {
			model.MemLabelIds = memLabelIds.String
		}
		if memRequirement.Valid {
			model.MemRequirement = memRequirement.String
		}
		if memEvaluation.Valid {
			model.MemEvaluation = memEvaluation.String
		}
		if memStatus.Valid {
			model.MemStatus = uint32(memStatus.Int64)
		}
		if memAddType.Valid {
			model.MemAddType = uint32(memAddType.Int64)
		}
		if memAddUseId.Valid {
			model.MemAddUseId = uint32(memAddUseId.Int64)
		}
		if memAddTime.Valid {
			model.MemAddTime = uint64(memAddTime.Int64)
		}
		if memEditTime.Valid {
			model.MemEditTime = uint64(memEditTime.Int64)
		}
		reply.ModelDbRpcMem = &model
		break
	}
	return
}

//根据查询条件查询会员信息
func (m *DbRpcMem) Find(ctx context.Context, args *grpcmodel.ModelDbRpcMemReqFind) (reply *grpcmodel.ModelDbRpcMemResFind, err error) {
	reply = &grpcmodel.ModelDbRpcMemResFind{}
	after_from_buf := bytes.Buffer{}
	params := make([]interface{}, 0)
	after_from_buf.WriteString("from dubmem m where m.memStatus>0")

	for {
		dbAccounts := NewDbProxy().Get(DB_Accounts)

		//一般条件查询
		if args.GetAddUseId() > 0 {
			after_from_buf.WriteString(" and m.memAddUseId=?")
			params = append(params, args.GetAddUseId())
		}

		if args.GetIsPage() && args.GetPageSize() > 1 {
			sql_str := fmt.Sprintf("select count(m.memId) %s", after_from_buf.String())
			m.log.Infof("def_func_db_mem.go Find method exec %s params is %+v\n", sql_str, params)
			row := dbAccounts.QueryRow(sql_str, params...)
			var total sql.NullInt64
			err := row.Scan(&total)
			if err != nil {
				m.log.Errorf("def_fun_db_mem.go Find method dbAccounts.QueryRow err. %v\n", err)
				reply.Err = 2
				break
			}
			if total.Valid {
				if total.Int64 < 1 {
					break
				}
				reply.Total = uint32(total.Int64)
			} else {
				break
			}

			after_from_buf.WriteString(" limit ?,?")
			params = append(params, args.GetStartRow(), args.GetPageSize())
		}

		sql_str := fmt.Sprintf("select m.memId, m.memName, m.memPhone, UNIX_TIMESTAMP(m.memBirthDate), m.memSex, m.memHeight, m.memEducation, m.memIncome, m.memJob,  m.memHouse, m.memCar,  m.memStatus, UNIX_TIMESTAMP(m.memAddTime) %s", after_from_buf.String())
		m.log.Infof("def_func_db_mem.go Find method exec %s params is %+v\n", sql_str, params)
		rows, err := dbAccounts.Query(sql_str, params...)
		switch {
		case err == sql.ErrNoRows:
			err = nil
			reply.Err = 1
		case err != nil:
			reply.Err = 2
			m.log.Errorf("def_fun_db_mem.go Find method dbAccounts.Query err. %v\n", err)
		default:
			//m.memId, m.memName, m.memPhone, m.memBirthDate, m.memSex, m.memHeight, m.memEducation, m.memIncome, m.memJob,  m.memHouse, m.memCar,  m.memStatus, m.memAddTime
			reply.Rows = make([]*grpcmodel.ModelDbRpcMem, 0)
			for rows.Next() {
				var (
					memId, memHeight, memIncome, memStatus, memBirthDate, memAddTime  sql.NullInt64
					memName, memPhone, memSex, memEducation, memJob, memHouse, memCar sql.NullString
				)
				err := rows.Scan(&memId, &memName, &memPhone, &memBirthDate, &memSex, &memHeight, &memEducation, &memIncome, &memJob, &memHouse, &memCar, &memStatus, &memAddTime)
				if err != nil {
					reply.Err = 2
					m.log.Errorf("def_fun_db_mem.go Find method rows.Scan err. %v\n", err)
				}
				tmp_mem := grpcmodel.ModelDbRpcMem{}
				if memId.Valid {
					tmp_mem.MemId = uint32(memId.Int64)
				}
				if memName.Valid {
					tmp_mem.MemName = memName.String
				}
				if memPhone.Valid {
					tmp_mem.MemPhone = memPhone.String
				}
				if memBirthDate.Valid {
					tmp_mem.MemBirthDate = uint64(memBirthDate.Int64)
				}
				if memSex.Valid {
					tmp_mem.MemSex = memSex.String
				}
				if memHeight.Valid {
					tmp_mem.MemHeight = uint32(memHeight.Int64)
				}
				if memEducation.Valid {
					tmp_mem.MemEducation = memEducation.String
				}
				if memIncome.Valid {
					tmp_mem.MemIncome = uint32(memIncome.Int64)
				}
				if memJob.Valid {
					tmp_mem.MemJob = memJob.String
				}
				if memHouse.Valid {
					tmp_mem.MemHouse = memHouse.String
				}
				if memCar.Valid {
					tmp_mem.MemCar = memCar.String
				}
				if memStatus.Valid {
					tmp_mem.MemStatus = uint32(memStatus.Int64)
				}
				if memAddTime.Valid {
					tmp_mem.MemAddTime = uint64(memAddTime.Int64)
				}
				reply.Rows = append(reply.Rows, &tmp_mem)
			}
		}
		break
	}
	return
}

var d_dbRpcMem *DbRpcMem

func NewDbRpcMem() *DbRpcMem {
	if d_dbRpcMem == nil {
		d_dbRpcMem = &DbRpcMem{
			log: utils.NewLogger(),
		}
	}
	return d_dbRpcMem
}
